Consumption Data Entry

The following data must be entered into the Consumption Data Entry sheet by the user:

• Date (Monthly);
• Degree Days;
• Sleepers Sold;
• Rooms Sold;
• Gas Consumption (kWh)
• Electricity Consumption (kWh) (This can be broken down into Daytime and Night readings);
• Water Consumption (m3).

Once this data has been entered into the table provided, performance charts will be plotted automatically on the spreadsheet labelled Simple Performance Charts. 

Simple Performance Charts

The performance charts include Electricity; Gas; Water; Sleepers and Rooms Sold; all plotted against the dates supplied by the user.  In addition to the graphs plotted on this sheet, the changes in performance for energy and water consumption are quantified and tabulated.

Data Analysis

Regression Selection

This spreadsheet involves the user selecting the Dependent and Independent variables which they want to analyse.  The data related to the variables selected will be tabulated accordingly on the sheet.

Single and Multi-Independent variable regressions can be carried out using a Microsoft Excel function.  To carry out the regression, the user must carry out the following steps:

• Select Tools window menu bar.
• Select Add Ins if the drop-down menu does not include a Data Analysis option.
• From the Add Ins option, a new window will appear.
• Tick the boxes for Analysis Toolpak and Analysis Toolpak VBA.
• Press OK.
• Select Tools from the window menu bar again.
• Select Data Analysis.
• A new window will appear. Select Regression from the list.
• Press OK.
• A new window will appear. Select the Input Y variable and highlight all of the cells, including the title, under the Dependent Variable cell.
• In the original window, select the Input X variable.
• Highlight all of the cells depending on how many independent variables you have chosen, including the title, for the Independent Variable.
• Tick the boxes for Labels and Residuals.
• Select the Output Range box.
• Select the Regression Output spreadsheet.
• Select the red cell as the output cell for the regression.
• Select OK in the window where all of the input data has been specified.
• If a new window appears, select OK.
• Select the Regression Output spreadsheet again, where the results from the regression will appear.

Regression Results

The regression analysis carried out for the Dependent Variables (Electricity, Gas and Water) can be tabulated in the Regression Results sheets provided for each variable.

The possible regression combinations that can be carried out have been entered into the available tables.  The user must simply fill in the white cells for the values required.

The Intercept and Coefficient values produced from the regression can be used to produce an equation for predicting a targeted consumption.  This is as follows:

Target Consumption (for Dependent Variable) = Intercept + Coefficient (1)*(Independent Variable (1)) + Coefficient (2)*(Independent Variable (2)) + Coefficient (3)*(Independent Variable (3)) + Coefficient (4)*(Independent Variable (4))

The values which determine the strength of the regression carried out are the R2 (Pearson Correlation coefficient), Significance Factor, and P value.  The R2 value indicates the strength of the relationship between the Dependent and Independent variables.  The closer this value is to 1, the better the relation.

The Significance Factor and the P value are reaffirming values for the strength of the relation.  If the P value is below 0.05, the Independent Variable is a significant factor affecting the consumption of the Dependent Variable being used.